from flask import Blueprint, jsonify, request
from src.models import Course, Teacher, Teaching, CourseRating, db, MajorPlan
import logging
import traceback
from decimal import Decimal
from src.models.db import get_db_cursor

# 配置日志
logger = logging.getLogger(__name__)

course_bp = Blueprint('course', __name__)

@course_bp.route('/', methods=['GET'])
def get_courses():
    try:
        logger.info("开始获取课程列表")
        
        # 第一阶段：必需的专业和年级筛选
        major_id = request.args.get('major_id')  # 专业编号
        grade = request.args.get('grade')        # 年级
        
        # 参数验证
        if not major_id or not grade:
            return jsonify({
                'status': 'error',
                'message': '缺少必要的参数：专业编号和年级'
            }), 400
            
        try:
            grade = int(grade)
        except (TypeError, ValueError):
            return jsonify({
                'status': 'error',
                'message': '年级参数必须是有效的整数'
            }), 400
        
        # 第二阶段：评价相关的筛选条件
        location = request.args.get('location')          # 上课地点
        score_level = request.args.get('score_level')    # 得分难度
        easy_level = request.args.get('easy_level')      # 水课程度

        # 构建基础查询
        query = """
            WITH avg_ratings AS (
                SELECT 
                    "课程编号",
                    "教师编号",
                    ROUND(AVG("得分高低（1到10）")::numeric, 2) as avg_score,
                    ROUND(AVG("水课程度（1到10）")::numeric, 2) as avg_easy,
                    COUNT(DISTINCT "评价编号") as rating_count
                FROM "课程评价表"
                GROUP BY "课程编号", "教师编号"
            )
            SELECT DISTINCT
                c."课程编号",
                c."课程名称",
                c."学分",
                string_agg(DISTINCT t."教室", ', ') as "教室",
                string_agg(DISTINCT t."教师编号", ', ') as "教师编号",
                ROUND(AVG(ar.avg_score)::numeric, 2) as "平均得分难度",
                ROUND(AVG(ar.avg_easy)::numeric, 2) as "平均水课程度",
                SUM(ar.rating_count) as "评价数量"
            FROM "课程表" c
            INNER JOIN "专业培养方案表" mp ON c."课程编号" = mp."课程编号"
            LEFT JOIN "授课表" t ON c."课程编号" = t."课程编号"
            LEFT JOIN avg_ratings ar ON c."课程编号" = ar."课程编号"
            WHERE mp."专业编号" = %s AND mp."年级" = %s
        """
        
        # 基础查询参数
        params = [major_id, grade]
        
        # 添加第二阶段筛选条件
        if location:
            query += ' AND t."教室" LIKE %s'
            params.append(f'%{location}%')
            
        if score_level:
            try:
                score_level = float(score_level)
                query += ' AND ar.avg_score >= %s'
                params.append(score_level)
            except (TypeError, ValueError):
                pass
            
        if easy_level:
            try:
                easy_level = float(easy_level)
                query += ' AND ar.avg_easy >= %s'
                params.append(easy_level)
            except (TypeError, ValueError):
                pass
            
        # 添加分组和排序
        query += """
            GROUP BY 
                c."课程编号", 
                c."课程名称", 
                c."学分"
            ORDER BY c."课程编号"
        """
        
        # 执行查询
        logger.info(f"执行SQL查询: {query}")
        logger.info(f"查询参数: {params}")
        
        with get_db_cursor() as cursor:
            cursor.execute(query, params)
            courses = cursor.fetchall()
            logger.info(f"查询到 {len(courses)} 条课程记录")
            
            # 获取列名
            column_names = ["课程编号", "课程名称", "学分", "教室", "教师编号", "平均得分难度", "平均水课程度", "评价数量"]
            
            # 处理查询结果
            result = []
            for course in courses:
                course_dict = {}
                for i, col_name in enumerate(column_names):
                    value = course[i]
                    if isinstance(value, Decimal):
                        course_dict[col_name] = float(value)
                    elif col_name in ["教室", "教师编号"] and value:
                        course_dict[col_name] = [x.strip() for x in value.split(',') if x.strip()]
                    elif isinstance(value, (int, float)):
                        course_dict[col_name] = float(value)
                    else:
                        course_dict[col_name] = value
                result.append(course_dict)
            
        logger.info(f"成功处理 {len(result)} 条课程数据")
        return jsonify({
            'status': 'success',
            'data': result
        })
    except Exception as e:
        logger.error(f"获取课程列表失败: {str(e)}")
        logger.error(f"错误详情:\n{traceback.format_exc()}")
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500

@course_bp.route('/<string:course_id>', methods=['GET'])
def get_course(course_id):
    try:
        # 从课程ID中提取原始课程编号和教师编号
        original_course_id, teacher_id = course_id.split('_')
        
        with get_db_cursor() as cursor:
            # 使用CTE获取评分信息
            cursor.execute("""
            WITH course_ratings AS (
                SELECT 
                        cr."课程编号",
                        cr."教师编号",
                        AVG(cr."得分高低（1到10）") as avg_score,
                        AVG(cr."水课程度（1到10）") as avg_water,
                    COUNT(*) as rating_count
                    FROM "课程评价表" cr
                    WHERE cr."课程编号" = %s AND cr."教师编号" = %s
                    GROUP BY cr."课程编号", cr."教师编号"
            )
            SELECT 
                c."课程编号",
                c."课程名称",
                c."学分",
                    t."教师编号",
                    t."教师姓名",
                    lt."教室",
                    lt."授课编号",
                    cr.avg_score as "得分高低",
                    cr.avg_water as "水课程度",
                    cr.rating_count as "评价数量"
            FROM "课程表" c
                JOIN "授课表" lt ON c."课程编号" = lt."课程编号"
                JOIN "教师表" t ON lt."教师编号" = t."教师编号"
                LEFT JOIN course_ratings cr ON c."课程编号" = cr."课程编号" AND t."教师编号" = cr."教师编号"
                WHERE c."课程编号" = %s AND t."教师编号" = %s
            """, (original_course_id, teacher_id, original_course_id, teacher_id))
        
        course = cursor.fetchone()
            
        if not course:
            return jsonify({
                'status': 'error',
                    'message': '未找到课程'
            }), 404
            
            course_dict = {
                '课程编号': f"{course[0]}_{course[3]}", # 组合课程编号和教师编号
                '原始课程编号': course[0],
                '课程名称': f"{course[1]} - {course[4]}班",
                '学分': float(course[2]) if course[2] else None,
                '教师编号': course[3],
                '教师姓名': course[4],
                '教室': course[5],
                '授课编号': course[6],
                '得分高低': float(course[7]) if course[7] else None,
                '水课程度': float(course[8]) if course[8] else None,
                '评价数量': course[9] if course[9] else 0
        }
        
        return jsonify({
            'status': 'success',
                'data': course_dict
        })
        
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500

@course_bp.route('/', methods=['POST'])
def create_course():
    """创建新课程"""
    data = request.get_json()
    
    # 验证数据
    if not data or not data.get('课程编号') or not data.get('课程名称') or not data.get('学分'):
        return jsonify({
            'status': 'error',
            'message': '缺少必要的课程信息'
        }), 400
        
    # 检查课程编号是否已存在
    if Course.query.get(data['课程编号']):
        return jsonify({
            'status': 'error',
            'message': '课程编号已存在'
        }), 400
        
    # 创建新课程
    course = Course(
        课程编号=data['课程编号'],
        课程名称=data['课程名称'],
        学分=data['学分']
    )
    
    try:
        db.session.add(course)
        db.session.commit()
        return jsonify({
            'status': 'success',
            'message': '课程创建成功',
            'data': {
                '课程编号': course.课程编号,
                '课程名称': course.课程名称,
                '学分': float(course.学分)
            }
        }), 201
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'status': 'error',
            'message': f'课程创建失败：{str(e)}'
        }), 500

@course_bp.route('/<string:course_id>', methods=['PUT'])
def update_course(course_id):
    """更新课程信息"""
    course = Course.query.get_or_404(course_id)
    data = request.get_json()
    
    if not data:
        return jsonify({
            'status': 'error',
            'message': '未提供更新数据'
        }), 400
        
    # 更新课程信息
    if '课程名称' in data:
        course.课程名称 = data['课程名称']
    if '学分' in data:
        course.学分 = data['学分']
        
    try:
        db.session.commit()
        return jsonify({
            'status': 'success',
            'message': '课程信息更新成功',
            'data': {
                '课程编号': course.课程编号,
                '课程名称': course.课程名称,
                '学分': float(course.学分)
            }
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'status': 'error',
            'message': f'课程信息更新失败：{str(e)}'
        }), 500

@course_bp.route('/<string:course_id>', methods=['DELETE'])
def delete_course(course_id):
    """删除课程"""
    course = Course.query.get_or_404(course_id)
    
    try:
        db.session.delete(course)
        db.session.commit()
        return jsonify({
            'status': 'success',
            'message': '课程删除成功'
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'status': 'error',
            'message': f'课程删除失败：{str(e)}'
        }), 500

@course_bp.route('/<string:course_id>/teachers', methods=['POST'])
def assign_teacher(course_id):
    """为课程分配教师"""
    course = Course.query.get_or_404(course_id)
    data = request.get_json()
    
    if not data or not data.get('教师编号') or not data.get('教室'):
        return jsonify({
            'status': 'error',
            'message': '缺少必要的授课信息'
        }), 400
        
    # 检查教师是否存在
    teacher = Teacher.query.get(data['教师编号'])
    if not teacher:
        return jsonify({
            'status': 'error',
            'message': '教师不存在'
        }), 404
        
    # 检查是否已经分配
    if Teaching.query.filter_by(
        教师编号=data['教师编号'],
        课程编号=course_id
    ).first():
        return jsonify({
            'status': 'error',
            'message': '该教师已分配此课程'
        }), 400
        
    # 创建授课记录
    teaching = Teaching(
        教师编号=data['教师编号'],
        课程编号=course_id,
        教室=data['教室']
    )
    
    try:
        db.session.add(teaching)
        db.session.commit()
        return jsonify({
            'status': 'success',
            'message': '教师分配成功',
            'data': {
                '授课编号': teaching.授课编号,
                '教师编号': teaching.教师编号,
                '教师姓名': teacher.教师姓名,
                '课程编号': teaching.课程编号,
                '教室': teaching.教室
            }
        }), 201
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'status': 'error',
            'message': f'教师分配失败：{str(e)}'
        }), 500

@course_bp.route('/ratings', methods=['POST'])
def add_rating():
    try:
        data = request.get_json()
        
        required_fields = ['课程编号', '教师编号', '得分高低（1到10）', '水课程度（1到10）']
        for field in required_fields:
            if field not in data:
                return jsonify({
                    'status': 'error',
                    'message': f'缺少必要字段：{field}'
                }), 400
        
        with get_db_cursor() as cursor:
            cursor.execute("""
            INSERT INTO "课程评价表" ("课程编号", "教师编号", "得分高低（1到10）", "水课程度（1到10）")
            VALUES (%s, %s, %s, %s)
            """, (
                data['课程编号'],
                data['教师编号'],
                data['得分高低（1到10）'],
                data['水课程度（1到10）']
            ))
        
        return jsonify({
            'status': 'success',
                'message': '评价添加成功'
        })
            
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500

@course_bp.route('/teachers', methods=['GET'])
def get_teachers():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM 教师")
        teachers = cursor.fetchall()
        
        result = []
        for teacher in teachers:
            teacher_dict = {
                '教师编号': teacher[0],
                '教师姓名': teacher[1]
            }
            result.append(teacher_dict)
            
        return jsonify({
            'status': 'success',
            'data': result
        })
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500

@course_bp.route('/grades', methods=['GET'])
def get_grades():
    """获取所有年级列表"""
    try:
        logger.info("开始获取年级列表")
        
        with get_db_cursor() as cursor:
            # 获取年级列表
            cursor.execute('SELECT DISTINCT "年级" FROM "专业培养方案表" ORDER BY "年级"')
            grades = cursor.fetchall()
            logger.info(f"查询到 {len(grades)} 个不同年级")
            
            result = []
            for grade in grades:
                result.append(grade[0])
                
            return jsonify({
                'status': 'success',
                'data': result
            })
    except Exception as e:
        logger.error(f"获取年级列表失败: {str(e)}")
        logger.error(f"错误详情:\n{traceback.format_exc()}")
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500

@course_bp.route('/init-test-data', methods=['GET', 'POST'])
def init_test_data():
    """初始化测试数据"""
    try:
        logger.info("开始初始化测试数据")
        db = get_db_connection()
        cursor = db.cursor()

        # 创建专业表（如果不存在）
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS "专业表" (
                "专业编号" character varying(20) PRIMARY KEY,
                "专业名称" character varying(50) NOT NULL
            )
        """)

        # 插入测试专业数据
        test_majors = [
            ('1', '计算机科学与技术'),
            ('2', '软件工程'),
            ('3', '人工智能')
        ]

        for major in test_majors:
            cursor.execute('SELECT COUNT(*) FROM "专业表" WHERE "专业编号" = %s', (major[0],))
            if cursor.fetchone()[0] == 0:
                cursor.execute(
                    'INSERT INTO "专业表" ("专业编号", "专业名称") VALUES (%s, %s)',
                    major
                )
                logger.info(f"插入专业: {major[1]}")

        # 创建课程表（如果不存在）
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS "课程表" (
                "课程编号" character varying(20) PRIMARY KEY,
                "课程名称" character varying(100) NOT NULL,
                "学分" numeric(4,2) NOT NULL
            )
        """)

        # 插入测试课程数据
        test_courses = [
            ('C001', '形势与政策 (2)', 0.25),
            ('C002', '大学物理B (1)', 4.00),
            ('C003', '大学物理实验B (1)', 1.50),
            ('C004', '面向对象程序设计与实践', 3.00),
            ('C005', '高等数学A (2)', 5.00)
        ]

        for course in test_courses:
            cursor.execute('SELECT COUNT(*) FROM "课程表" WHERE "课程编号" = %s', (course[0],))
            if cursor.fetchone()[0] == 0:
                cursor.execute(
                    'INSERT INTO "课程表" ("课程编号", "课程名称", "学分") VALUES (%s, %s, %s)',
                    course
                )
                logger.info(f"插入课程: {course[1]}")

        # 创建教师表（如果不存在）
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS "教师表" (
                "教师编号" character varying(20) PRIMARY KEY,
                "教师姓名" character varying(50) NOT NULL
            )
        """)

        # 插入测试教师数据
        test_teachers = [
            ('T001', '教师1'),
            ('T047', '教师2'),
            ('T073', '教师3')
        ]

        for teacher in test_teachers:
            cursor.execute('SELECT COUNT(*) FROM "教师表" WHERE "教师编号" = %s', (teacher[0],))
            if cursor.fetchone()[0] == 0:
                cursor.execute(
                    'INSERT INTO "教师表" ("教师编号", "教师姓名") VALUES (%s, %s)',
                    teacher
                )
                logger.info(f"插入教师: {teacher[1]}")

        # 创建授课表（如果不存在）
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS "授课表" (
                "授课编号" SERIAL PRIMARY KEY,
                "教师编号" character varying(20),
                "课程编号" character varying(20),
                "教室" character varying(100),
                FOREIGN KEY ("教师编号") REFERENCES "教师表" ("教师编号"),
                FOREIGN KEY ("课程编号") REFERENCES "课程表" ("课程编号")
            )
        """)

        # 插入测试授课数据
        test_teachings = [
            ('T001', 'C001', '星期五 中山邦翰楼 104'),
            ('T047', 'C002', '星期一 力行楼 4302'),
            ('T073', 'C003', '星期六 物理学院 3305-3')
        ]

        for teaching in test_teachings:
            cursor.execute(
                """
                INSERT INTO "授课表" ("教师编号", "课程编号", "教室")
                SELECT %s, %s, %s
                WHERE NOT EXISTS (
                    SELECT 1 FROM "授课表" WHERE "教师编号" = %s AND "课程编号" = %s
                )
                """,
                (*teaching, teaching[0], teaching[1])
            )
            if cursor.rowcount > 0:
                logger.info(f"插入授课记录: {teaching[0]} - {teaching[1]}")

        # 创建专业培养方案表（如果不存在）
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS "专业培养方案表" (
                "方案编号" SERIAL PRIMARY KEY,
                "专业编号" character varying(20) NOT NULL,
                "年级" integer NOT NULL,
                "课程编号" character varying(20) NOT NULL,
                FOREIGN KEY ("课程编号") REFERENCES "课程表" ("课程编号"),
                FOREIGN KEY ("专业编号") REFERENCES "专业表" ("专业编号")
            )
        """)

        # 插入测试培养方案数据
        test_plans = [
            ('1', 24, 'C001'),
            ('1', 24, 'C002'),
            ('1', 24, 'C003')
        ]

        for plan in test_plans:
            cursor.execute(
                """
                INSERT INTO "专业培养方案表" ("专业编号", "年级", "课程编号")
                SELECT %s, %s, %s
                WHERE NOT EXISTS (
                    SELECT 1 FROM "专业培养方案表" 
                    WHERE "专业编号" = %s AND "年级" = %s AND "课程编号" = %s
                )
                """,
                (*plan, *plan)
            )
            if cursor.rowcount > 0:
                logger.info(f"插入培养方案: {plan[0]} - {plan[2]}")

        db.commit()
        logger.info("测试数据初始化完成")

        return jsonify({
            'status': 'success',
            'message': '测试数据初始化成功'
        })

    except Exception as e:
        db.rollback()
        logger.error(f"测试数据初始化失败: {str(e)}")
        logger.error(f"错误详情:\n{traceback.format_exc()}")
        return jsonify({
            'status': 'error',
            'message': str(e)
        }), 500 